package com.export.service.dynamic;

import com.export.entity.ReportParam;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

/**
 * 账号产品报表动态sql
 *
 * @author yuanqinglong
 * @since 2021/7/27 9:32
 */
@Component
public class AccountProductReportDynamicStatement {


    //**********************************账号接口报表*************************************//




    /**
     * 账户接口报告导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String accountProductReportExportSql(ReportParam reportParam) {
        String sql = "SELECT\n" +
                "a.aut_keyid,org_type,\n" +
                "stat_prod_uid AS prod_uid,\n" +
                "MAX(stat_prod_name) AS prod_name,\n" +
                "CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id,\n" +
                "MAX(c.cust_sname) as cust_sname,\n" +
                "SUM(stat_req_num) as stat_req_num,\n" +
                "SUM(stat_findout_num) as stat_findout_num,\n" +
                "SUM(stat_fail_num) as stat_fail_num,\n" +
                "SUM(stat_charge_num) as stat_charge_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o on o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c on c.cust_id = o.org_cust_id\n" +
                "LEFT JOIN sys.sys_authon a on sta.stat_org_id = a.aut_org_id\n" +
                "LEFT JOIN sys.sys_prod p on p.prod_uid = sta.stat_prod_uid WHERE stat_date BETWEEN #{startDate} AND #{endDate}";
        sql += this.accountProductReportConditions(reportParam);
        sql += " GROUP BY c.cust_id, a.aut_keyid,org_type, stat_prod_uid ";
        return sql;
    }





    /**
     * 账户接口报告条件
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    private String accountProductReportConditions(ReportParam reportParam) {
        String sql = "";
        if (StringUtils.isNotBlank(reportParam.getAutKeyid())) {
            sql += " AND aut_keyid LIKE #{%autKeyid%}";
        }
        if (StringUtils.isNotBlank(reportParam.getCustId())) {
            sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) LIKE #{%custId%}";
        }
        if (StringUtils.isNotBlank(reportParam.getProdUid())) {
            sql += " AND prod_uid LIKE #{%prodUid%}";
        }
        return sql;
    }





    /**
     * 产品报告详细列表导出
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String accountProductReportDetailExportList(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        String sql = "SELECT\n" +
                "TO_CHAR(stat_date,'" + grouping + "') AS stat_date,\n" +
                "a.aut_keyid,\n" +
                "stat_prod_uid AS prod_uid,org_type,\n" +
                "MAX(stat_prod_name) AS prod_name,\n" +
                "cust_sname, CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num,\n" +
                "SUM(stat_charge_num) AS stat_charge_num "+
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o ON o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c ON c.cust_id = o.org_cust_id\n" +
                "LEFT JOIN sys.sys_authon a ON sta.stat_org_id = a.aut_org_id\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "WHERE aut_keyid = #{autKeyid} AND prod_uid = #{prodUid} AND stat_date BETWEEN #{startDate} AND #{endDate} ";
        sql += " GROUP BY c.cust_id,a.aut_keyid,org_type,stat_prod_uid,\n" +
                "TO_CHAR(stat_date,'" + grouping + "') ORDER BY prod_uid";
        return sql;
    }



}
